#!/usr/bin/env python
# -*- encoding: utf-8 -*-
'''
@File    :   Excel.py
@Time    :   2024/01/16 16:08:42
@Author  :   chakcy 
@Email   :   947105045@qq.com
@description   :   excel 文件操作
'''

import pandas as pd

import sqlite3
from abc import ABC, abstractmethod
import os

# 将 sqlite 内的数据转换为 excel 文件
class Excel(ABC):
    # 初始化
    def __init__(self, path, db_name:str, table_name, day):
        self.table_name = table_name
        self.db_name = db_name
        self.path = path
        self.conn = sqlite3.connect(os.path.join(path, "../", db_name))
        self.df = pd.read_sql(f"select * from {self.table_name} WHERE date('now', '-{day} day') <= date(date)", con = self.conn)
        self.conn.commit()
        self.conn.close()

    def excelSave(self, path, df:pd.DataFrame):
        if os.path.exists(path):
            with pd.ExcelWriter(path, engine='openpyxl', mode='a', if_sheet_exists='overlay') as writer:
                df.to_excel(writer,sheet_name=self.table_name, index=False)
        else:
            df.to_excel(path, sheet_name=self.table_name, index=False)
            
    # 将所有数据转换为 excel 表头为数据库字段
    def dataToExcelAll(self):
        excel_file_path = os.path.join(self.path, f"{self.db_name.split('.')[0]}All.xlsx")
        self.excelSave(excel_file_path, self.df)

    # 自定义筛选规则，转换为 excel 文件
    @abstractmethod
    def dataToExcel(self, columns:dict):
        pass

# 读取 Excel 配置文件
def readParams(path):
    df_params = pd.read_excel(path, "Sheet1", dtype=str)
    return df_params.to_dict("records")


if __name__ == "__main__":
    print(readParams(r"../params.xlsx"))
